﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.IO;
using System.Windows.Forms;

namespace Hdt.TransOcean.Dal
{
    class SqlDataAccessHelper
    {
        private String connectionString;

        public String ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }
        public String GetConnectString()
        {
            //SqlDataAccessHelper helper = new SqlDataAccessHelper();
            //helper.ConnectionString =this.
            Dao d = new Dao();
            connectionString = d.connectionString();
            return connectionString;
        }

        public int ExecuteNoneQuery(string strSql)
        { 
            int result  = 0;
            String  ConnString = GetConnectString();
            SqlConnection connect = new SqlConnection(ConnString);
            connect.Open();
            SqlCommand sqlCmd=new SqlCommand(strSql, connect);
            sqlCmd.CommandType = CommandType.Text;
            SqlTransaction sqlTrans ;
            sqlTrans = connect.BeginTransaction();
            try
            {
                System.Data.SqlClient.SqlParameter paramter1 = sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.SmallInt,4);
                paramter1.Direction = ParameterDirection.ReturnValue;
                sqlCmd.ExecuteNonQuery();
                sqlTrans.Commit();
                result = (int) paramter1.Value;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            finally
            {
                sqlCmd.Dispose();
                sqlTrans.Dispose();
                connect.Close();
                sqlCmd = null;
            }

            return result ;
        }

        public int ExecuteNoneQuery(String spName, String[] parms, Object[] vals)
        {
            int result = 0;
            String ConnString = GetConnectString();
            SqlConnection connect = new SqlConnection(ConnString);
            connect.Open();
            SqlTransaction sqlTrans;
            sqlTrans = connect.BeginTransaction();
            SqlCommand sqlCmd = new SqlCommand(spName, connect, sqlTrans);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                for (int i = 0; i < parms.Length; i++)
                {
                    if (parms[i] != "")
                    {
                        sqlCmd.Parameters.Add(new SqlParameter(parms[i], vals[i]));
                    }
                }

                System.Data.SqlClient.SqlParameter paramter1 = sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.SmallInt, 4);
                paramter1.Direction = ParameterDirection.ReturnValue;
                sqlCmd.ExecuteNonQuery();
                sqlTrans.Commit();
                result = (int)paramter1.Value;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            finally
            {
                sqlCmd.Dispose();
                sqlTrans.Dispose();
                connect.Close();
                sqlCmd = null;
            }

            return result;
        }

        public Object ExecuteNoneQueryOutputPara(String spName, String[] parms, Object[] vals)
        {
            Object result = 0;
            String ConnString = GetConnectString();
            SqlConnection connect = new SqlConnection(ConnString);
            connect.Open();
            SqlTransaction sqlTrans;
            sqlTrans = connect.BeginTransaction();
            SqlCommand sqlCmd = new SqlCommand(spName, connect, sqlTrans);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                for (int i = 0; i < parms.Length; i++)
                {
                    if (parms[i] != "")
                    {
                        sqlCmd.Parameters.Add(new SqlParameter(parms[i], vals[i]));
                    }
                }
                System.Data.SqlClient.SqlParameter paramter1 = sqlCmd.Parameters.Add("@returnValue", SqlDbType.NVarChar, 50);
                paramter1.Direction = ParameterDirection.Output;
                sqlCmd.ExecuteNonQuery();
                result = sqlCmd.Parameters["@returnValue"].Value;
                sqlTrans.Commit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            finally
            {
                sqlCmd.Dispose();
                sqlTrans.Dispose();
                connect.Close();
                sqlCmd = null;
            }

            return result;
        }

        public DataSet SP_FillDataSet(String spName, String[] parms, Object[] vals)
        {
            DataSet ds = new DataSet();
            String ConnString = GetConnectString();
            SqlConnection connect = new SqlConnection(ConnString);
            SqlCommand sqlCmd = new SqlCommand(spName, connect);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            int i = 0;
            for (i = 0; i < parms.Length; i++)
            {
                if (parms[i] != "")
                {
                    sqlCmd.Parameters.Add(new SqlParameter(parms[i], vals[i]));
                }
            }
            SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
            da.Fill(ds);
            return ds;
        }

        public DataTable SP_FillDataTable(String spName, String[] parms, Object[] vals)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            ds = SP_FillDataSet(spName, parms, vals);
            if (ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }

            return dt;
        }

        public DataTableCollection SP_FillDataTableReturnCollect(String spName, String[] parms, Object[] vals)
        {
            DataTableCollection dt =null;//= new DataTableCollection();
            DataSet ds = new DataSet();
            ds = SP_FillDataSet(spName, parms, vals);
            if (ds.Tables.Count > 0)
            {
               // dt = new DataTable[ds.Tables.Count];
                dt = ds.Tables;
            }
            return dt;
        }

        public int Sp_Update(String spName, String[] parms, Object[] vals)
        {
            int result = 0;
            SqlCommand sqlCmd;
            SqlTransaction sqlTrans;
            SqlConnection connect;
            try
            {
                String ConnString = GetConnectString();
                connect = new SqlConnection(ConnString);
                connect.Open();
                sqlCmd = new SqlCommand(spName, connect);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlTrans = connect.BeginTransaction();
                sqlCmd.Transaction = sqlTrans;
                try
                {
                    for (int i = 0; i < parms.Length; i++)
                    {
                        if (parms[i] != "")
                        {
                            sqlCmd.Parameters.Add(new SqlParameter(parms[i], vals[i]));
                        }

                    }
                    System.Data.SqlClient.SqlParameter paramter1 = sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.SmallInt, 4);
                    paramter1.Direction = ParameterDirection.ReturnValue;
                    sqlCmd.ExecuteNonQuery();
                    sqlTrans.Commit();
                    result = (int)paramter1.Value;
                }
                catch (Exception ex)
                {
                    sqlTrans.Rollback();
                    throw new System.InvalidOperationException(ex.ToString());
                }
                finally
                {
                    sqlCmd.Dispose();
                    sqlTrans.Dispose();
                    connect.Close();
                    sqlCmd = null;
                }
            }
            catch (Exception ex)
            {
                //sqlTrans.Rollback();
                throw new System.InvalidOperationException(ex.ToString());
            }

            return result;

        }


    }// end  class SqlDataAccessHelper
}
